-- -------------------------------------------------
--    Project Name:   干线车辆操作时效监控报表-汇总数据
--    File Name：     jms_dwd.dwd_tms_trunk_oper_summary_dt
--    Job Name:      jms_dwd__dwd_tms_trunk_operation_summary
--    Description :   计算每个干线在每个站点的卸车包数、票数、散件数、最大操作时长、最小操作时长、平均操作时长
--    Author :        Enzo | 禹铄
--    date：          2021-11-17 11:30
-- -------------------------------------------------
with tmp as (                                                -- 取T-2天的明细数据进行汇总
    select a.shipment_no,
           a.shipment_name,
           a.setout_arrive_sitename,
           a.setout_sitename,
           a.setout_sitecode,
           a.arrive_sitename,
           a.arrive_sitecode,
           a.billcode,
           a.packagecode,
           a.arrive_scantime,
           a.operation_duration,
           a.actual_arrival_time
    from jms_dwd.dwd_tms_trunk_oper_eff_dt a
    where a.dt = date_add('{{ execution_date | cst_ds }}', -1)
    and a.operation_duration between 0 and 1000  -- 默认操作时间不超过1000分钟
),
     question_express as (                                      -- 汇总有问题的快件单号（问题件表、退回件表、拦截件表）
         select billcode
         from (
                  select a.billcode as billcode
                  from jms_dwd.dwd_barscan_difficult_dt a       -- 问题件分区表 取前三天
                  where a.dt <= '{{ execution_date | cst_ds }}'
                    and a.dt > date_add('{{ execution_date | cst_ds }}', -3)
                    and a.dimtype = 'bill_scansite'
                  union all
                  select b.waybill_no as billcode
                  from jms_ods.tab_reback_transfer_express b     -- 退转件快照表 取最新一天
                  where  b.dt = '{{ execution_date | cst_ds }}'
                    and 1 = (case
                                 when b.entry_type = 1 then     --当 entry_type =1表示拦截件退回，当status = 待审核or已审核 即（1 or 2）算有效退转数据
                                     if(b.status in (1, 2), 1, 0)
                                 when b.entry_type = 2 then     --当entry_type =2表示问题件退回，当status = 已审核即（2）算有效退转数据
                                     if(b.status = 2, 1, 0)
                                 else 0 end)
                  union all
                  select c.waybill_no as billcode
                  from jms_ods.yl_oms_interceptorpiece c        -- 拦截件快照表 取最新一天（status =1 or 2 都算拦截件数据）
                  where c.dt = '{{ execution_date | cst_ds }}'
                    and c.status in (1, 2)
              ) tmp_que
         group by billcode
     )

insert overwrite table jms_dwd.dwd_tms_trunk_oper_summary_dt partition(dt)
select m.shipment_no,                                       -- 任务号
       m.shipment_name,                                     -- 任务名字
       m.setout_arrive_sitename,                            -- 发出及到达站点名
       m.setout_sitename,                                   -- 发出站点名字
       m.setout_sitecode,                                   -- 发出站点代码
       m.arrive_sitename,                                   -- 到站站点名字
       m.arrive_sitecode,                                   -- 到站站点代码
       m.tickets,                                           -- 票数
       m.packages,                                          -- 包数
       m.bulks,                                             -- 散件数
       m.packages + m.bulks                as pkg_bulks,    -- 散件数+包数
       n.min_operation_duration,                            -- 最小操作时间
       n.max_operation_duration,                            -- 最大操作时间
       n.avg_operation_duration,                            -- 平均操作时间
       cast(m.actual_arrival_time as date) as dt            -- 分区
from (select tmp.shipment_no,
             tmp.shipment_name,
             tmp.setout_arrive_sitename,
             tmp.setout_sitename,
             tmp.setout_sitecode,
             tmp.arrive_sitename,
             tmp.arrive_sitecode,
             tmp.actual_arrival_time,
             count(distinct
                   case when substring(tmp.billcode, 0, 1) = 'B' then null else tmp.billcode end) as tickets,  -- 票数
             count(distinct
                   case when tmp.packagecode = '--' then null else tmp.packagecode end)           as packages, -- 袋数
             count(distinct
                   case
                       when tmp.packagecode = '--' and substring(tmp.billcode, 0, 2) = 'JT' then tmp.billcode
                       end)                                                                       as bulks     -- 散件数
      from tmp
      group by tmp.shipment_no, tmp.shipment_name, tmp.setout_arrive_sitename, tmp.setout_sitename, tmp.setout_sitecode,
               tmp.arrive_sitename, tmp.arrive_sitecode, tmp.actual_arrival_time) m
         join (select tmp.shipment_no,
                      tmp.shipment_name,
                      tmp.setout_arrive_sitename,
                      tmp.setout_sitename,
                      tmp.setout_sitecode,
                      tmp.arrive_sitename,
                      tmp.arrive_sitecode,
                      min(tmp.operation_duration)           as min_operation_duration,                      -- 最小操作时间
                      max(tmp.operation_duration)           as max_operation_duration,                      -- 最大操作时间
                      round(avg(tmp.operation_duration), 2) as avg_operation_duration                       -- 平均操作时间
               from tmp
               where 0 = (select count(1) as num from question_express where tmp.billcode = question_express.billcode) -- 问题件排除
                 and tmp.arrive_scantime is not null -- 卸车未发车排除
               group by tmp.shipment_no, tmp.shipment_name, tmp.setout_arrive_sitename, tmp.setout_sitename,
                        tmp.setout_sitecode, tmp.arrive_sitename, tmp.arrive_sitecode) n
              on m.shipment_no = n.shipment_no and m.arrive_sitecode = n.arrive_sitecode